var express = require('express');
var app = express();
app.use('/public', express.static('public'));

var mysql = require('mysql');
var jdbc = require('./jdbc');
var env = 'dev';// 默认dev数据库

// 缓存表对应的列
var cacheTableColumn = {};

function getConnect() {
    var connection = mysql.createConnection(jdbc[env]);
    connection.connect();
    return connection;
}

String.prototype.replaceAll = function (s1, s2) {
    return this.replace(new RegExp(s1, "gm"), s2);
};

app.get('/', function (req, res) {
    res.sendFile(__dirname + "/" + "index.html");
});

app.get('/relation', function (req, res) {
    res.sendFile(__dirname + "/" + "relation.html");
});

app.get('/tool', function (req, res) {
    res.sendFile(__dirname + "/" + "tool.html");
});

app.get('/queryEnv', function (req, res) {
    var data = {};
    data.env = env;
    data.envList = [];
    for (var e in jdbc) {
        data.envList.push(e);
    }
    console.log('--------------------------queryEnv----------------------------');
    console.log(data);
    console.log('------------------------------------------------------------\n\n');
    res.end(JSON.stringify(data));
});

app.get('/changeEnv', function (req, res) {
    env = req.query.env;
    console.log('--------------------------changeEnv----------------------------');
    console.log('env: ' + env);
    console.log('------------------------------------------------------------\n\n');
    res.end(JSON.stringify({env: env}));
});

app.get('/queryAllTable', function (req, res) {
    var sql = 'show tables';
    console.log(sql);
    var connection = getConnect();
    connection.query(sql, function (err, result) {
        var data = [];
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
        } else {
            for (var i in result) {
                for (var j in result[i]) {
                    data.push(result[i][j])
                }
            }
        }
        console.log('--------------------------queryAllTable----------------------------');
        console.log(data);
        console.log('------------------------------------------------------------\n\n');
        res.end(JSON.stringify(data));
    });
    connection.end();
});

app.get('/queryColumnsByTableName', function (req, res) {
    var tableName = req.query.tableName;
    if (!!tableName) {
        var sql = 'show full columns from ' + tableName;
        console.log(sql);
        var connection = getConnect();
        connection.query(sql, function (err, result) {
            var data = [];
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
            } else {
                for (var i in result) {
                    data.push(result[i]['Field'])
                }
            }
            console.log('--------------------------queryColumnsByTableName----------------------------');
            console.log(data);
            console.log('------------------------------------------------------------\n\n');
            cacheTableColumn[tableName] = data;
            res.end(JSON.stringify(data));
        });
        connection.end();
    }
});

app.get('/queryBySql', function (req, res) {
    var sqlParamArr = req.query.sql.split("|");
    var data = {};
    data.result = [];
    if (sqlParamArr.length >= 2 && !!sqlParamArr[0]) {

        var tableName = sqlParamArr[0];
        var columns = sqlParamArr[1];
        var where = sqlParamArr[2].replaceAll(":", " ");

        // 没有选择列时
        var orderColumn;
        if (!columns) {
            columns = "*";
            orderColumn = cacheTableColumn[tableName][0];
        } else {
            orderColumn = columns.split(",")[0];
        }

        var sql = "select "
            + columns
            + " from "
            + tableName
            + " where delete_flag=0 "
            + where
            + " order by "
            + orderColumn
            + " desc, update_time desc"
            + " limit 0,5";

        data.sql = sql;
        console.log(sql + "\n");
        var connection = getConnect();
        connection.query(sql, function (err, result) {
            if (err) {
                console.log('[SELECT ERROR] - ', err.message);
            } else {
                data.result = result;
            }
            console.log('--------------------------queryBySql----------------------------');
            console.log(data);
            console.log('------------------------------------------------------------\n\n');
            res.end(JSON.stringify(data));
        });
        connection.end();
    } else {
        res.end(JSON.stringify(data));
    }
});

app.get('/queryByStr', function (req, res) {
    var sql = req.query.sql;
    var data = {};
    data.sql = sql;
    console.log(sql + "\n");
    var connection = getConnect();
    connection.query(sql, function (err, result) {
        if (err) {
            console.log('[SELECT ERROR] - ', err.message);
            data.result = [];
        } else {
            data.result = result;
        }
        console.log('--------------------------queryByStr----------------------------');
        console.log(data);
        console.log('------------------------------------------------------------\n\n');
        res.end(JSON.stringify(data));
    });
    connection.end();
});


var syncQuery = function (sql, connection) {
    // 返回一个 Promise
    return new Promise((resolve, reject) => {
        connection.query(sql, (err, result) => {
            if (err) {
                reject(err)
            } else {
                resolve(result)
            }
        })
    })
};

app.get('/queryRelation', async function (req, res) {

    var id = req.query.id;
    var data = {
        name: "关系图",
        children: []
    };

    if(!!id){
        var modelData, naviData, footData, moduleData, htmlBgData, moduleItemData = [], moduleStyleData = [];

        var modelSql = "select * from mana_web_model where model_id=" + id;
        var naviSql = "select * from mana_navi where model_id=" + id;
        var footSql = "select * from mana_foot where model_id=" + id;
        var moduleSql = "select * from mana_module where model_id=" + id;
        var htmlBgSql = "select * from mana_html_bg where model_id=" + id;

        console.log('--------------------------查询relation信息----------------------------');
        var connection = getConnect();
        console.log('--------------------------查询model信息----------------------------');
        modelData = await syncQuery(modelSql, connection);
        console.log(modelData);
        if(modelData.length>0){
            console.log('--------------------------查询module信息----------------------------');
            moduleData = await syncQuery(moduleSql, connection);
            console.log(moduleData);

            console.log('--------------------------查询moduleItem和style信息----------------------------');
            for (var i = 0; i < moduleData.length; i++) {
                var moduleId = moduleData[i]["module_id"];
                var moduleItemSql = "select * from mana_module_item where module_id=" + moduleId;

                var result = await syncQuery(moduleItemSql, connection);
                moduleItemData.push(result);

                var moduleStyleSql = "select * from mana_module_style where module_id=" + moduleId;
                result = await syncQuery(moduleStyleSql, connection);
                moduleStyleData.push(result);
            }

            moduleData = moduleData.map(item => {
                return {name: "module\n"+item.module_id}
            });

            for (var i = 0; i < moduleData.length; i++) {
                var module = moduleData[i];
                module.name += '\n'+moduleStyleData[i][0].every_width;
                module.children = moduleItemData[i].map(item => {
                    return {
                        name: "module_item\n"+item.module_item_id
                    }
                });
            }

            data = {
                name: "model\n"+modelData[0].model_id,
                children: moduleData
            };
        }
    }

    console.log(data);
    console.log('------------------------------------------------------------\n\n');
    res.end(JSON.stringify(data));

    connection.end();
});

var server = app.listen(8081, function () {
    var host = "localhost";
    var port = server.address().port;
    console.log("应用实例，访问地址为 http://%s:%s", host, port)
});